Time Zone Conversion
For deployments of ebs that are hosted in a time zone different to the institute, a number of functions are available to aid with time zone conversion.
Date and time information is stored in the database without any time zone information. Auto-generated date and time information will be assumed to be in the infrastructure time zone. The infrastructure time zone is contained in a hidden institution setting that is automatically populated based on the institution type at installation.
Note: The infrastructure time zone cannot be changed without a data migration exercise.
The institution's time zone/region is stored in the Institution time zone institution setting (on the General (Cross-System) screen). Note that the solution only supports a single institute time zone.
Note: The list of time zones is sourced from the EBS_TIMEZONES database table, which is populated from the relevant RDBMS data source.
The following set of SQL functions (that can be used in the ontrack Designer modules, Reports, ebs: intel and so on) can be used to convert dates from infrastructure to institute time zones:
-
ebs_tz_pkg.ConvertDateTimeInfraToInst - converts a simple date/time data type from an infrastructure time zone to an institute time zone
-
ebs_tz_pkg.GetDateTimeTZAtInstitute - converts a simple date/time data type into a data type including institute time zone information, optionally converting the original date from the infrastructure time zone
-
ebs_tz_pkg.GetDateTimeISO - converts a date/time data type including time zone information into an ISO8601 compatible string
Please note the following when considering time zone conversion:
-
You must understand the data that is being viewed to decide on whether it should be converted (for example: an exam date/time will be captured in institute time and will not require conversion, whereas a trigger-populated created data will be captured in infrastructure time and will require conversion).
-
Unpredictable results will be given if a database column contains a mixture of both auto-generated and manually set data.